/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myconfession.dao;

import myconfession.utils.SQLConnection;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import javax.xml.bind.JAXBException;
import myconfession.dto.userpage.UserPage;
import myconfession.dto.userpage.UserPageList;
import myconfession.utils.ConstantProvider;
import myconfession.utils.XMLUtility;

/**
 *
 * @author Liem
 */
public class UserPageDAO {

    public static UserPageList getAllUserPage() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        UserPageList list = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("exec sp_getAllUserPage");
            rs = stm.executeQuery();
            list = new UserPageList();
            while (rs.next()) {
                String username = rs.getString("username");
                int pageId = rs.getInt("pageId");
                String role = rs.getString("role");
                String pageName = rs.getString("pageName");
                String fbPageId = rs.getString("fbPageId");
                String description = rs.getString("description");
                boolean isActive = rs.getBoolean("isActive");
                UserPage up = new UserPage();
                up.setUsername(username);
                up.setPageId(new BigInteger(pageId + ""));
                up.setPagename(pageName);
                up.setFbPageId(fbPageId);
                up.setDescription(description);
                up.setRole(role);
                up.setIsActive(isActive);
                list.getUserPage().add(up);
            }
        } catch (NamingException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public static boolean checkExistUserOnPage(int pageId, String user) throws JAXBException {
        UserPageList ups = (UserPageList) XMLUtility.unmarshallJAXB(ConstantProvider.getInstance().getUserpageXmlPath(),
                UserPageList.class);
        for (UserPage tmp : ups.getUserPage()) {
            if (tmp.getUsername().trim().equals(user) && tmp.getPageId() == (new BigInteger(pageId + ""))) {
                return true;
            }
        }
        return false;
    }

    public static boolean insertUserPage(UserPage us) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("exec sp_addUserPage ?,?,?");
            stm.setString(1, us.getUsername());
            stm.setInt(2, us.getPageId().intValue());
            stm.setString(3, us.getRole());
            int rs = stm.executeUpdate();
            if (rs > 0) {
                return true;
            }
        } catch (NamingException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return false;
    }

    public static boolean deleteUserPage(UserPage us) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM UserPage WHERE username = ? and pageId = ?");
            stm.setString(1, us.getUsername());
            stm.setInt(2, us.getPageId().intValue());
            int rs = stm.executeUpdate();
            if (rs > 0) {
                return true;
            }
        } catch (NamingException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UserPageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return false;
    }
}
